﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XQ.QRCodeThrough.DAL.Model;
using XQ.QRCodeThrough.DAL.Model.Business;
using XQ.QRCodeThrough.DAL.Models;

namespace XQ.QRCodeThrough.DAL.DAL.Common
{
    public class ConsultingDal:BaseDal<Consulting>
    {
        public List<ConsultingModel> GetList(ConsultingSearchModel ssm, Pagination page, TokenModel token)
        {
            using (QRCodeThroughEntities qtEntities = new QRCodeThroughEntities())
            {
                //获取用户信息
                Sys_Users user = userDal.FindModel(u => u.id == token.UID);
                //找出所有标签相关工程ID
                List<int?> projectIDs = new List<int?>();
                List<Project_Label> pls = projectLabelDal.FindAllModel(pl => ssm.ProjectLabelIDs.Contains((int)pl.dictID));
                if (pls != null && pls.Count > 0)
                {
                    projectIDs = pls.Select(pl => pl.projectID).Distinct().ToList();
                }
                string projectIdStr = string.Join(",", projectIDs);

                //获取工程分类ID和对应子分类ID
                List<int> projectTypIDs = new List<int>();
                if (ssm.ProjectTypeIDs != null && ssm.ProjectTypeIDs.Count > 0)
                {
                    projectTypIDs.AddRange(ssm.ProjectTypeIDs);
                    foreach (int pID in ssm.ProjectTypeIDs)
                    {
                        List<int> dictIDs = dictDal.FindAllModel(d => d.ParentId == pID).Select(d => d.ID).ToList();
                        projectTypIDs.AddRange(dictIDs);
                    }
                }


                //分页信息
                StringBuilder sqlPage = new StringBuilder();
                sqlPage.Append(" select count(1) ");
                sqlPage.Append(" from ProjectInfo p");
                sqlPage.Append(" where p.informant is null ");
                if (token.RoleId > 0)
                {
                    List<int> departmentIds = departmentDal.GetAllChildDeptIDs(token.DepartmentID);
                    departmentIds.Add(token.DepartmentID);
                    //第二部门
                    int? departmentID2 = userDal.FindModel(u => u.id == token.UID).DepartmentID2;
                    if (departmentID2 > 0)
                    {
                        departmentIds.AddRange(departmentDal.GetAllChildDeptIDs((int)departmentID2));
                        departmentIds.Add((int)departmentID2);
                    }
                    //查询出用户多关联的部门
                    List<Sys_User_Department> uds = userDepartmentDal.FindAllModel(ud => ud.userid == token.UID);
                    if (uds != null && uds.Count > 0)
                    {
                        List<int> otherDepartmentIDs = uds.Select(ud => (int)ud.departmentid).ToList();
                        if (otherDepartmentIDs != null && otherDepartmentIDs.Count > 0)
                        {
                            departmentIds.AddRange(otherDepartmentIDs);
                        }
                    }
                    if (roleDal.IsDepartmentRole(token.RoleId))
                    {
                        sqlPage.Append(" and (");
                        sqlPage.Append("  p.departmentID in (" + string.Join(",", departmentIds) + ")");
                        if (token.RoleId == 49)
                        {
                            sqlPage.Append(" or p.id in ( select projectid  from ProblemHandling where receiveDepartmentID = " + token.DepartmentID + ")");
                            sqlPage.Append(" or p.id in ( select projectid  from Project_Department where departmentID = " + token.DepartmentID + ")");
                        }
                        sqlPage.Append(" )");
                    }
                }
                //小区用户只看本小区
                if (user != null && user.isCommunity == true)
                {
                    sqlPage.Append(" and case when p.projectType in(3546,3547,3548,3549,3550,3551,3552,3553,3554,3555,3556,3557) then charindex(p.enterpriseName,'" + user.TrueName + "') else charindex(p.residentialName,'" + user.TrueName + "') + charindex(p.address,'" + user.TrueName + "') end >0");
                }
                //关联部门
                if (ssm.OtherDepartmentID > 0)
                {
                    sqlPage.Append(" and p.id in ( select projectid  from Project_Department where departmentID = " + ssm.OtherDepartmentID + ")");
                }
                //工程标签
                if (ssm.ProjectLabelIDs != null && ssm.ProjectLabelIDs.Count > 0)
                {
                    sqlPage.Append(" and p.id in(" + projectIdStr + ") ");
                }
                //工程状态
                if (ssm.ProjectState > 0)
                {
                    switch (ssm.ProjectState)
                    {

                        case 1:
                            sqlPage.Append(" and p.isExamine is null ");
                            break;
                        case 2:
                            sqlPage.Append(" and p.isExamine=1 and p.confirmUserID is null ");
                            break;
                        case 3:
                            sqlPage.Append(" and p.confirmUserID >0 ");
                            break;
                    }
                }
                //工程名称
                if (!string.IsNullOrEmpty(ssm.ProjectName))
                {
                    sqlPage.Append(" and p.ProjectName like '%" + ssm.ProjectName + "%'");
                }
                //工程编号
                if (!string.IsNullOrEmpty(ssm.ProjectNo))
                {
                    sqlPage.Append(" and p.no like '%" + ssm.ProjectNo + "%'");
                }
                //所属村、社区
                if (ssm.DepartmentID > 0)
                {
                    List<int> departmentIds = departmentDal.GetAllChildDeptIDs(ssm.DepartmentID);
                    departmentIds.Add(ssm.DepartmentID);
                    sqlPage.Append(" and p.departmentID in (" + string.Join(",", departmentIds) + ")");
                }
                //开工日期起
                if (ssm.StartDateStart.Year != 1970)
                {
                    sqlPage.Append(" and p.startDate >= '" + ssm.StartDateStart + "'");
                }
                //开工日期止
                if (ssm.StartDateEnd.Year != 2999)
                {
                    sqlPage.Append(" and p.startDate <= '" + ssm.StartDateEnd.ToString("yyyy-MM-dd") + " 23:59:59'");
                }
                //拟竣工日期起
                if (ssm.EndDateStart.Year != 1970)
                {
                    sqlPage.Append(" and p.endDate >= '" + ssm.EndDateStart + "'");
                }
                //拟竣工日期止
                if (ssm.EndDateEnd.Year != 2999)
                {
                    sqlPage.Append(" and p.endDate <= '" + ssm.EndDateEnd.ToString("yyyy-MM-dd") + " 23:59:59'");
                }

                //备案日期起
                if (ssm.SubmitDateStart.Year != 1970)
                {
                    sqlPage.Append(" and p.addTime >= '" + ssm.SubmitDateStart + "'");
                }
                //备案日期止
                if (ssm.SubmitDateEnd.Year != 2999)
                {
                    sqlPage.Append(" and p.addTime <= '" + ssm.SubmitDateEnd.ToString("yyyy-MM-dd") + " 23:59:59'");
                }
                //建设单位
                if (!string.IsNullOrEmpty(ssm.OwnerUnit))
                {
                    sqlPage.Append(" and (p.OwnerUnit like '%" + ssm.OwnerUnit + "%' or p.ownerUnitHead like '%" + ssm.OwnerUnit + "%' or p.ownerUnitPhone like '%" + ssm.OwnerUnit + "%')");
                }

                int allCount = mwe.Database.SqlQuery<int>(sqlPage.ToString()).First();
                page.RecordCount = allCount;
                page.PageCount = (int)Math.Ceiling((decimal)page.RecordCount / (decimal)ssm.pageSize);
                page.PageIndex = ssm.index;
                page.PageSize = ssm.pageSize;

                //数据列表
                StringBuilder sql = new StringBuilder();
                sql.Append("select top " + ssm.pageSize + " * from ");
                sql.Append(" (select row_number() over(order by p.id desc) as rowNum, ");
                sql.Append(" p.*,");
                sql.Append(" (select title from sys_dics where id=p.projectType) as ProjectTypeName,");
                sql.Append(" case when DATEDIFF(day,p.addtime,getdate())>3 and isExamine is null then 'red'else '' end as lineColor,");
                //or (DATEDIFF(day,(select top 1 addtime from ProblemHandling where projectid=p.id order by id desc),getdate())>10) 
                sql.Append(" case when p.confirmUserID is not null and p.id in (select projectID from ProblemHandling where problemlevel=2533) and isCheck is null then 1 else 0 end as IsShowCheckButton,");
                sql.Append(" (select count(1) from ProblemHandling where projectid=p.id) as PatrolNum, ");
                sql.Append(" ((select departmentName from Sys_Departments where id=(select parentID from Sys_Departments where id=p.departmentID))+'/'+(select departmentName from Sys_Departments where id=departmentID)) as departmentName");
                sql.Append(" from ProjectInfo p ");
                sql.Append(" where p.informant is null ");
                //关联部门
                if (ssm.OtherDepartmentID > 0)
                {
                    sql.Append(" and p.id in ( select projectid  from Project_Department where departmentID = " + ssm.OtherDepartmentID + ")");
                }
                //工程标签
                if (ssm.ProjectLabelIDs != null && ssm.ProjectLabelIDs.Count > 0)
                {
                    sql.Append(" and p.id in(" + projectIdStr + ") ");
                }
                //工程状态
                if (ssm.ProjectState > 0)
                {
                    switch (ssm.ProjectState)
                    {

                        case 1:
                            sql.Append(" and p.isExamine is null ");
                            break;
                        case 2:
                            sql.Append(" and p.isExamine=1 and p.confirmUserID is null ");
                            break;
                        case 3:
                            sql.Append(" and p.confirmUserID >0 ");
                            break;
                    }
                }
                //工程名称
                if (!string.IsNullOrEmpty(ssm.ProjectName))
                {
                    sql.Append(" and p.ProjectName like '%" + ssm.ProjectName + "%'");
                }
                //工程编号
                if (!string.IsNullOrEmpty(ssm.ProjectNo))
                {
                    sql.Append(" and p.no like '%" + ssm.ProjectNo + "%'");
                }
                //楼盘名称
                if (!string.IsNullOrEmpty(ssm.ResidentialName))
                {
                    sql.Append(" and p.ResidentialName like '%" + ssm.ResidentialName + "%'");
                }
                //是否为特殊类装饰工程
                if (ssm.IsSpecial > 0)
                {
                    switch (ssm.IsSpecial)
                    {
                        case 1:
                            sql.Append(" and p.isStructuralChanges=1");
                            break;
                        case 2:
                            sql.Append(" and p.isFunctionalAdjustment=1");
                            break;
                        case 3:
                            sql.Append(" and p.isFireFightingChanges=1");
                            break;
                        case 4:
                            sql.Append(" and p.isOtherChange=1");
                            break;
                    }
                }
                sql.Append(") t1 ");
                sql.Append("where rowNum>" + (ssm.index - 1) * ssm.pageSize);
                LogBll.Instance.WriteLog(LogBll.LogType.Search, "", token.UID, "获取工程列表SQL", "", "", "", sql.ToString());
                return mwe.Database.SqlQuery<ProjectModel>(sql.ToString()).ToList();
            }
        }

    }
}
